/*
 * Copyright (c) 2014, Hsu Shih-Chia (schsu01@gmail.com)
 * All rights reserved.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *  http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.qood;

import java.util.*;

/**
 * QSQL is similar to a <a href="http://en.wikipedia.org/wiki/SQL#Queries">SELECT query</a> in SQL.
 * <p/>
 * <p>Query in current version has 8 parts, here's an example:
 * <pre><code>
 *   SELECT [DISTINCT]                   //{@link #distinct(boolean)}
 *     ..., ... <a href="http://en.wikipedia.org/wiki/Alias_(SQL)">AS</a> ...                   //{@link #select(String...)}
 *   <a href="http://en.wikipedia.org/wiki/From_(SQL)">FROM</a> ...                            //{@link #from(String)}
 *   <a href="http://en.wikipedia.org/wiki/Join_(SQL)">LEFT [OUTER |INNER] JOIN</a> ... ON ... //{@link #hashJoin(String, boolean, String...)}
 *   WHERE ...                           //{@link #where(QWhere)}
 *   GROUP BY ...                        //{@link #groupBy(String...)}
 *   <a href="http://en.wikipedia.org/wiki/Having_(SQL)">HAVING</a> ...                          //{@link #having(QWhere)}
 *   <a href="http://en.wikipedia.org/wiki/Order_by">ORDER BY</a> ...                        //{@link #orderBy(String...)}
 * </code></pre>
 *
 * @author SCHsu01
 * @version 1.0
 */
public interface QSQL {
  /**
   * Set whether remove duplicated data in SELECTed columns or formulas
   *
   * @param unique option
   * @return a reference to this Object
   */
  QSQL distinct(boolean unique);

  /**
   * Add a list of columns or formulas to SELECT part
   *
   * @param formulas columns or formulas
   * @return a reference to this Object
   */
  QSQL select(String... formulas);

  /**
   * Add a column or formula to SELECT part with a new column name.
   * You can directly use alias as an existing column in next SELECT part.
   *
   * @param alias   new column name
   * @param formula column or formula
   * @return a reference to this Object
   */
  QSQL selectAs(String alias, String formula);

  /**
   * Add a list of columns or formulas to SELECT part with a list of new column names
   * You can directly use alias as an existing column in next SELECT element.
   *
   * @param alias    new column names
   * @param formulas columns or formulas
   * @return a reference to this Object
   */
  QSQL selectAs(String[] alias, String... formulas);

  /**
   * Set the alias of FROM part, which can be referenced in else QSQL parts
   *
   * @param name alias of source data
   * @return a reference to this Object
   */
  QSQL from(String name);

  /**
   * Add a join relation with the same match conditions in both FROM(left) and JOIN(right)
   *
   * @param name         alias of this JOIN, which can be referenced in else QSQL parts
   * @param innerJoin    option to determine whether perform inner join or outer join
   * @param matchFormula define columns or fomulas in both FROM(left) and JOIN(right) for matching
   * @return a reference to this Object
   */
  QSQL hashJoin(String name, boolean innerJoin, String... matchFormula);

  /**
   * Add a join relation with different match conditions in FROM(left) and JOIN(right)
   *
   * @param name         alias of this JOIN, which can be referenced in else QSQL parts
   * @param innerJoin    option to determine whether perform inner join or outer join
   * @param mainFormula  define columns or fomulas in FROM(left) part for matching
   * @param matchFormula define columns or fomulas in JOIN(right) part for matching
   * @return a reference to this Object
   */
  QSQL hashJoin(String name, boolean innerJoin, String[] mainFormula, String... matchFormula);

  /**
   * Set the WHERE part, which can filter data rows before GROUP BY
   *
   * @param where filter rule
   * @return a reference to this Object
   */
  QSQL where(QWhere where);

  /**
   * Add a list of columns or formulas to GROUP BY part, the aggregation rule
   *
   * @param formulas columns or formulas
   * @return a reference to this Object
   */
  QSQL groupBy(String... formulas);

  /**
   * Set the HAVING part, which can filter data rows after GROUP BY
   *
   * @param where filter rule
   * @return a reference to this Object
   */
  QSQL having(QWhere where);

  /**
   * Add a list of selected column number, columns or formulas to ORDER BY part, the sorting rule
   *
   * @param formulas selected column number, columns or formulas
   * @return a reference to this Object
   */
  QSQL orderBy(String... formulas);

  /**
   * Add a selected column number, column or formula to ORDER BY part, the sorting rule
   *
   * @param formula selected column number, column or formula
   * @param desc    whether with descending sort
   * @return a reference to this Object
   */
  QSQL orderBy(String formula, boolean desc);

  /**
   * Add a list of selected column number, columns or formulas to ORDER BY part, the sorting rule
   *
   * @param formulas selected column number, columns or formulas
   * @param desc     whether with descending sort
   * @return a reference to this Object
   */
  QSQL orderBy(String[] formulas, boolean... desc);

  /**
   * Remove specified addable QSQL parts like SELECT, JOIN, GROUP BY or ORDER BY
   *
   * @param select  whether remove the definition of SELECT part
   * @param join    whether remove the definition of JOIN part
   * @param groupBy whether remove the definition of GROUP BY part
   * @param orderBy whether remove the definition of ORDER BY part
   * @return a reference to this Object
   */
  QSQL clear(boolean select, boolean join, boolean groupBy, boolean orderBy);
}

final class SQL implements QSQL {
  private final Map<String, String> _select = new LinkedHashMap<String, String>();
  private final List<String> _groupBy = new ArrayList<String>(), _orderBy = new ArrayList<String>();
  private final Map<String, Join> _join = new LinkedHashMap<String, Join>();
  private final BoolList _orderDesc = new BoolList();
  private boolean _distinct;
  private String _from;
  private Where _where, _having;

  SQL() {/*call getInstance()*/}

  public SQL distinct(boolean unique) {
    _distinct = unique;
    return this;
  }

  public SQL select(String... cols) {
    for (final String col : cols) _select.put(col, col);
    return this;
  }

  public SQL selectAs(String alias, String formula) {
    _select.put(alias, formula);
    return this;
  }

  public SQL selectAs(String[] alias, String... formulas) {
    final int n = alias.length;
    if (n != formulas.length)
      throw new IllegalArgumentException(Arrays.asList(alias) + " not match " + Arrays.asList(formulas));
    for (int i = 0; i < n; i++) _select.put(alias[i], formulas[i]);
    return this;
  }

  public SQL from(String name) {
    _from = name;
    return this;
  }

  public SQL hashJoin(String name, boolean innerJoin, String... matchFormula) {
    return hashJoin(name, innerJoin, matchFormula, matchFormula);
  }

  public SQL hashJoin(String name, boolean innerJoin, String[] mainFormula, String... matchFormula) {
    if (null == name) throw new IllegalArgumentException("hashJoin name required");
    _join.put(name, new Join(name, innerJoin, mainFormula, matchFormula));
    return this;
  }

  public SQL where(QWhere where) {
    _where = (Where) where;
    return this;
  }

  public SQL groupBy(String... formulas) {
    _groupBy.addAll(Arrays.asList(formulas));
    return this;
  }

  public SQL having(QWhere where) {
    _having = (Where) where;
    return this;
  }

  public SQL orderBy(String... formulas) {
    _orderBy.addAll(Arrays.asList(formulas));
    _orderDesc.addAll(BoolList.nCopies(formulas.length, false));
    return this;
  }

  public SQL orderBy(String formula, boolean desc) {
    _orderBy.add(formula);
    _orderDesc.add(desc);
    return this;
  }

  public SQL orderBy(String[] formulas, boolean... desc) {
    final int na = formulas.length, nb = desc.length;
    for (int i = 0; i < na; i++) {
      _orderBy.add(formulas[i]);
      _orderDesc.add(i < nb && desc[i]);
    }
    return this;
  }

  public SQL clear(boolean select, boolean join, boolean groupBy, boolean orderBy) {
    if (select) _select.clear();
    if (join) _join.clear();
    if (groupBy) _groupBy.clear();
    if (orderBy) {
      _orderBy.clear();
      _orderDesc.clear();
    }
    return this;
  }

  @Override
  public String toString() {
    final StringBuilder sb = new StringBuilder("SELECT ");
    if (_distinct) sb.append("DISTINCT ");
    if (_select.isEmpty()) sb.append('*');
    else {
      boolean first = true;
      String formula, alias;
      for (final Map.Entry<String, String> entry : _select.entrySet()) {
        if (first) first = false;
        else sb.append(", ");
        sb.append(formula = entry.getValue());
        if (!formula.equals(alias = entry.getKey())) sb.append(' ').append(alias);
      }
    }
    sb.append("\nFROM ");
    if (null == _from) sb.append("<>");
    else sb.append('<').append(_from).append('>');
    for (final Join join : _join.values()) sb.append('\n').append(join);
    if (null != _where) sb.append("\nWHERE ").append(_where);
    if (!_groupBy.isEmpty()) sb.append("\nGROUP BY ").append(_groupBy);
    if (null != _having) sb.append("\nHAVING ").append(_having);
    if (!_orderBy.isEmpty()) {
      sb.append("\nORDER BY ");
      for (int i = 0, n = _orderBy.size(); i < n; i++) {
        if (i > 0) sb.append(", ");
        sb.append(_orderBy.get(i));
        if (_orderDesc.get(i)) sb.append(" DESC");
      }
    }
    return sb.toString();
  }

  Collection<String> usedSource() {
    final Collection<String> result = new LinkedHashSet<String>();
    result.add(_from);
    result.addAll(_join.keySet());
    return result;
  }

  Model result(View view) {//key feature
    return new Executor(view).getResult();
  }

  private static final class Join {
    private final String alias;
    private final boolean inner;
    private final String[] main, join;

    private Join(String alias, boolean inner, String[] main, String[] join) {
      this.alias = alias;
      this.inner = inner;
      this.main = main;
      this.join = join;
    }

    @Override
    public String toString() {
      final StringBuilder sb = new StringBuilder("LEFT ").append(inner ? "INNER" : "OUTER")
        .append(" JOIN <").append(alias).append("> ON ");
      if (main == join) sb.append(Arrays.asList(main));
      else sb.append(Arrays.asList(main)).append('=').append(Arrays.asList(join));
      return sb.toString();
    }
  }

  private final class Executor {
    private final Temp model;

    Executor(View view) {
      this.model = new Temp(view, _from);
      model.initCols(_from, model.main);
    }

    private Model getResult() {
      final int rowsBeforeGroup = getJoined(model.main.getRowCount());
      IntRead rows = IntRead.nSize(rowsBeforeGroup);
      if (null != _where) rows = getFiltered(_where.createFilter(model), rowsBeforeGroup);
      if (_groupBy.isEmpty()) model.setGrouped(null, null, null);
      else rows = IntRead.nSize(getGrouped(rows));
      Filter having = null;//might group
      if (null != _having) having = _having.createFilter(model);
      final String[] alias;
      final Col[] select;//might group
      if (_select.isEmpty()) select = model.getCols(alias = model.getDefaultCols());
      else {
        final Map<String, String> copy = new LinkedHashMap<String, String>(_select.size());
        for (Map.Entry<String, String> entry : _select.entrySet()) {
          final String as = entry.getKey(), fn = entry.getValue();
          if (!fn.endsWith("*")) copy.put(as, fn);
          else model.putAliasCols(copy, 1 == fn.length() ? null : fn.substring(0, fn.length() - 2), null);
        }
        select = model.getCols(alias = StringUtil.toArray(copy.keySet()), StringUtil.toArray(copy.values()));
      }
      Sort sort = null;//might group
      if (!_orderBy.isEmpty()) sort = getSortRule(select);
      rows = model.doSummary(rows, select);
      if (null != having) rows = getFiltered(having, rows.size());
      if (null != sort) rows = IntRead.array(SortInt.sort(rows.toArray(), sort));
      return Output.getPartial(alias, select, rows, _distinct);
    }

    private Sort getSortRule(Col[] select) {
      final int orders = _orderBy.size();
      final Collection<Col> cols = new ArrayList<Col>(orders);
      final BoolList desc = new BoolList(orders);
      for (int i = 0; i < orders; i++) {
        final String byName = _orderBy.get(i);
        final Col col = model.getCol(byName);
        if (col.isConstant()) {
          if (!StringUtil.isNumber(byName)) continue;//order by constant column
          final int index = TypeUtil.toInt(col.getRow(0));
          if (index > 0 && index <= select.length) cols.add(select[index - 1]);
          else throw new IllegalArgumentException("order by " + index + " in " + model.view);
        } else cols.add(col);
        desc.add(_orderDesc.get(i));
      }
      return new Sort(Col.toArray(cols), desc.toArray());
    }

    private IntRead getFiltered(final Filter filter, int rows) {
      final IntList filtered = IntList.create(rows >> 1);//half size
      for (int i = 0; i < rows; i++) {
        final Boolean result = filter.is(i);
        if (null == result) break;//stop all filtering
        if (result) filtered.add(i);//for each row
      }
      return filtered.size() == rows ? IntRead.nSize(rows) : filtered.toRead();//filtered
    }

    @SuppressWarnings("unchecked")
    private int getGrouped(final IntRead rows) {
      final String[] byName = StringUtil.toArray(_groupBy);
      final Col[] byCol = model.getCols(byName);
      final Map<Object[], IntList> groupRow = CollFactory.ARRAY.getMap(true);
      final int byCols = byCol.length;
      final Object[] key = new Object[byCols];//reuse
      for (int i = 0, n = rows.size(); i < n; i++) {
        final int row = rows.get(i);
        IntList groupRows = groupRow.get(getValues(byCols, row, byCol, key));
        if (null == groupRows) groupRow.put(key.clone(), groupRows = IntList.create(/*unknown size*/));
        groupRows.add(row);
      }
      final int groups = groupRow.size();
      final IntRead[] groupRows = new IntRead[groups];
      final int[] keyId = new int[groups];
      final Iterator<IntList> groupIter = groupRow.values().iterator();
      for (int i = 0; i < groups; i++) keyId[i] = (groupRows[i] = groupIter.next().toRead()).get(0);//take first
      model.setGrouped(byName, Col.getPartial(IntRead.array(keyId), byCol), groupRows);//key action
      return groups;
    }

    @SuppressWarnings("unchecked")
    private void checkType(Class a, Class b, int i, Join info) {
      if (!(a.equals(b) || a.isAssignableFrom(b) || b.isAssignableFrom(a)))
        throw new UnknownFormatConversionException(info.main[i] + "<" + a.getSimpleName() +
          "> join " + info.join[i] + "<" + b.getSimpleName() + "> in " + model.view);
    }

    private int getJoined(final int rows) {
      final int joins = _join.size();
      if (0 == joins) return rows;//no join needed
      final Join[] join = _join.values().toArray(new Join[joins]);
      final Model[] srcJoins = new Model[joins];
      final IntList[][] joinRows = new IntList[joins][];
      final BitSet skip = new BitSet(rows);
      for (int j = 0; j < joins; j++) {//for each join
        final Join info = join[j];
        final String alias = info.alias;
        final Model src = srcJoins[j] = model.getSource(alias);
        if (null == src) throw new IllegalStateException("join source <" + alias + "> not found in " + model.view);
        model.initCols(alias, src);//so we can use getJoinCols() to process formula
        final Col[] col = model.getJoinCols(_from, info.main), colJ = model.getJoinCols(info.alias, info.join);
        final int cols = col.length;
        final Object[] values = new Object[cols];//reuse
        for (int i = 0; i < cols; i++) checkType(col[i].getType(), colJ[i].getType(), i, info);//for each join col
        final Map<Object[], IntList> map = getMapping(src.getRowCount(), colJ);
        final IntList[] jr = joinRows[j] = new IntList[rows];
        if (skip.isEmpty()) if (info.inner) for (int i = 0; i < rows; i++) {
          if (null == (jr[i] = map.get(getValues(cols, i, col, values)))) skip.set(i);
        }
        else for (int i = 0; i < rows; i++) jr[i] = map.get(getValues(cols, i, col, values));
        else if (info.inner) for (int i = 0; i < rows; i++) {
          if (!skip.get(i) && null == (jr[i] = map.get(getValues(cols, i, col, values)))) skip.set(i);
        }
        else for (int i = 0; i < rows; i++) if (!skip.get(i)) jr[i] = map.get(getValues(cols, i, col, values));
      }
      final IntList[] results = new IntList[joins + 1], joinRow = new IntList[joins];
      for (int i = 0; i <= joins; i++) results[i] = IntList.create(/*unknown size*/);
      final int[] combine = new int[joins];
      final IntList EMPTY = IntList.valueOf(-1);//Note: will cause IntRead contains -1, which means null mapping
      if (skip.isEmpty()) for (int i = 0; i < rows; i++) {
        for (int j = 0; j < joins; j++) {//for each join
          IntList joined = joinRows[j][i];
          joinRow[j] = null == joined ? EMPTY : joined;
        }
        addJoinRow(i, joins, results, joinRow, combine, 0);//recursive add (with main = i)
      }
      else for (int i = 0; i < rows; i++) {
        if (skip.get(i)) continue;
        for (int j = 0; j < joins; j++) {//for each join
          IntList joined = joinRows[j][i];
          joinRow[j] = null == joined ? EMPTY : joined;
        }
        addJoinRow(i, joins, results, joinRow, combine, 0);//recursive add (with main = i)
      }
      final IntList result = results[joins];//main = the last one
      model.initCols(_from, model.main, result);//key action
      for (int j = 0; j < joins; j++) model.initCols(join[j].alias, srcJoins[j], results[j]);//key action
      return result.size();
    }

    private void addJoinRow(int mainId, int joinCnt, IntList[] result, IntList[] joinRow, int[] combine, int level) {
      if (level == joinCnt) {
        for (int i = 0; i < joinCnt; i++) result[i].add(joinRow[i].get(combine[i]));
        result[joinCnt].add(mainId);
      } else for (int i = 0, n = joinRow[level].size(), next = level + 1; i < n; i++) {
        combine[level] = i;
        addJoinRow(mainId, joinCnt, result, joinRow, combine, next);
      }
    }

    private Object[] getValues(int cols, int row, Col[] col, Object[] result) {
      for (int i = 0; i < cols; i++) result[i] = col[i].getRow(row);
      return result;
    }

    private Map<Object[], IntList> getMapping(int rows, Col[] col) {//not cached
      final Map<Object[], IntList> result = CollFactory.ARRAY.getMap(false);
      final int cols = col.length;
      final Object[] key = new Object[cols];//reuse
      for (int i = 0; i < rows; i++) {
        IntList list = result.get(getValues(cols, i, col, key));
        if (null == list) result.put(key.clone(), list = IntList.create(/*unknown size*/));
        list.add(i);
      }
      return result;
    }
  }
}